{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "d8962ac4",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "96f3da2e",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\tjd\\AppData\\Local\\Temp\\ipykernel_16768\\2049530591.py:2: DtypeWarning: Columns (32) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  data = pd.read_csv(path)\n"
     ]
    }
   ],
   "source": [
    "path = \"../OECD.SDD.NAD,DSD_NAMAIN10@DF_TABLE2,2.0+all.csv\"\n",
    "data = pd.read_csv(path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "82de0cf1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(301281, 44)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "bc8f5dc4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['STRUCTURE', 'STRUCTURE_ID', 'STRUCTURE_NAME', 'ACTION', 'FREQ',\n",
       "       'Frequency of observation', 'REF_AREA', 'Reference area', 'SECTOR',\n",
       "       'Institutional sector', 'COUNTERPART_SECTOR',\n",
       "       'Counterpart institutional sector', 'TRANSACTION', 'Transaction',\n",
       "       'INSTR_ASSET', 'Financial instruments and non-financial assets',\n",
       "       'ACTIVITY', 'Economic activity', 'EXPENDITURE', 'Expenditure',\n",
       "       'UNIT_MEASURE', 'Unit of measure', 'PRICE_BASE', 'Price base',\n",
       "       'TRANSFORMATION', 'Transformation', 'TABLE_IDENTIFIER',\n",
       "       'Table identifier', 'TIME_PERIOD', 'Time period', 'OBS_VALUE',\n",
       "       'Observation value', 'REF_YEAR_PRICE', 'Price reference year',\n",
       "       'CONF_STATUS', 'Confidentiality status', 'DECIMALS', 'Decimals',\n",
       "       'OBS_STATUS', 'Observation status', 'UNIT_MULT', 'Unit multiplier',\n",
       "       'CURRENCY', 'Currency'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "02580109",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "REF_AREA good\n",
      "Reference area good\n",
      "TRANSACTION good\n",
      "Transaction good\n",
      "UNIT_MEASURE good\n",
      "Unit of measure good\n",
      "PRICE_BASE good\n",
      "Price base good\n",
      "TIME_PERIOD good\n",
      "OBS_VALUE good\n",
      "REF_YEAR_PRICE good\n",
      "OBS_STATUS good\n",
      "Observation status good\n",
      "UNIT_MULT good\n",
      "Unit multiplier good\n",
      "CURRENCY good\n",
      "Currency good\n"
     ]
    }
   ],
   "source": [
    "for col in data.columns:\n",
    "    content = data[col].unique()\n",
    "    if len(content) == 1:\n",
    "        data.drop(col, axis=1, inplace=True)\n",
    "    else:\n",
    "        print(col, 'good')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "730964ec",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['REF_AREA', 'Reference area', 'TRANSACTION', 'Transaction',\n",
       "       'UNIT_MEASURE', 'Unit of measure', 'PRICE_BASE', 'Price base',\n",
       "       'TIME_PERIOD', 'OBS_VALUE', 'REF_YEAR_PRICE', 'OBS_STATUS',\n",
       "       'Observation status', 'UNIT_MULT', 'Unit multiplier', 'CURRENCY',\n",
       "       'Currency'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "70494f95",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "REF_AREA >>> ['CMR' 'GBR' 'IRL' 'CHE' 'ITA' 'RUS' 'CAN' 'LVA' 'FRA' 'AUT' 'GEO' 'NOR'\n",
      " 'PRT' 'LTU' 'HUN' 'CRI' 'USA' 'SWE' 'EA20' 'AUS' 'BEL' 'ROU' 'SVK' 'CHL'\n",
      " 'SVN' 'ESP' 'SEN' 'GRC' 'FIN' 'CHN' 'EU27_2020' 'COL' 'HRV' 'ZAF' 'DEU'\n",
      " 'MEX' 'NLD' 'ISR' 'CZE' 'BRA' 'EST' 'JPN' 'DEU_F' 'TUR' 'POL' 'DNK' 'LUX'\n",
      " 'NZL' 'EU15' 'MAR' 'HKG' 'KOR' 'SAU' 'SGP' 'ISL' 'MLT' 'IDN' 'OECDE'\n",
      " 'OECD26' 'BGR' 'OECD' 'CPV' 'IND' 'SRB' 'MKD' 'CYP' 'ALB']\n",
      "Reference area >>> ['Cameroon' 'United Kingdom' 'Ireland' 'Switzerland' 'Italy' 'Russia'\n",
      " 'Canada' 'Latvia' 'France' 'Austria' 'Georgia' 'Norway' 'Portugal'\n",
      " 'Lithuania' 'Hungary' 'Costa Rica' 'United States' 'Sweden'\n",
      " 'Euro area (20 countries)' 'Australia' 'Belgium' 'Romania'\n",
      " 'Slovak Republic' 'Chile' 'Slovenia' 'Spain' 'Senegal' 'Greece' 'Finland'\n",
      " 'China (People’s Republic of)'\n",
      " 'European Union (27 countries from 01/02/2020)' 'Colombia' 'Croatia'\n",
      " 'South Africa' 'Germany' 'Mexico' 'Netherlands' 'Israel' 'Czechia'\n",
      " 'Brazil' 'Estonia' 'Japan' 'Former Federal Republic of Germany' 'Türkiye'\n",
      " 'Poland' 'Denmark' 'Luxembourg' 'New Zealand'\n",
      " 'European Union (15 countries)' 'Morocco' 'Hong Kong (China)' 'Korea'\n",
      " 'Saudi Arabia' 'Singapore' 'Iceland' 'Malta' 'Indonesia' 'OECD Europe'\n",
      " 'OECD (26 countries)' 'Bulgaria' 'OECD' 'Cabo Verde' 'India' 'Serbia'\n",
      " 'North Macedonia' 'Cyprus' 'Albania']\n",
      "TRANSACTION >>> ['B1GQXOTGL' 'P51C' 'B6N' 'NP' 'B9' 'D9B' 'IN21D' 'IN1C' 'IN1B' 'IN21B'\n",
      " 'B6G' 'B5G' 'B1GQ' 'P3' 'P5' 'D9C' 'B8N' 'IN1D' 'B5N' 'D8' 'OTGL' 'IN21C'\n",
      " 'B5N_POP' 'D9D']\n",
      "Transaction >>> ['Gross domestic income' 'Consumption of fixed capital'\n",
      " 'Disposable income, net'\n",
      " 'Acquisitions less disposals of non-financial non-produced assets'\n",
      " 'Net lending (+) / net borrowing (-)'\n",
      " 'Net capital transfers from the rest of the world'\n",
      " 'Current transfers payable to the rest of the world'\n",
      " 'Primary income receivable from the rest of the world'\n",
      " 'Net primary income from the rest of the world'\n",
      " 'Net current transfers from the rest of the world'\n",
      " 'Disposable income, gross'\n",
      " 'Balance of primary incomes, gross / National income, gross'\n",
      " 'Gross domestic product' 'Final consumption expenditure'\n",
      " 'Gross capital formation'\n",
      " 'Capital transfers receivable from the rest of the world' 'Saving, net'\n",
      " 'Primary income payable to the rest of the world'\n",
      " 'Balance of primary incomes, net / National income, net'\n",
      " 'Adjustment for the change in pension entitlements'\n",
      " 'Trading gain or loss'\n",
      " 'Current transfers receivable from the rest of the world'\n",
      " 'Balance of primary incomes, net / National income, net, per capita'\n",
      " 'Capital transfers payable to the rest of the world']\n",
      "UNIT_MEASURE >>> ['XDC' 'USD_PPP' 'USD_EXC' 'USD_PPP_PS']\n",
      "Unit of measure >>> ['National currency' 'US dollars, PPP converted'\n",
      " 'US dollars, exchange rate converted'\n",
      " 'US dollars per person, PPP converted']\n",
      "PRICE_BASE >>> ['V' 'L' 'LR' 'VQ' 'Y']\n",
      "Price base >>> ['Current prices' 'Chain linked volume' 'Chain linked volume (rebased)'\n",
      " 'Current prices (constant converter)' 'Previous year prices']\n",
      "TIME_PERIOD >>> [2007 1996 1995 1994 1993 2006 2005 2004 2003 2002 2001 2000 1999 1998\n",
      " 1997 2021 2020 2019 2018 2017 2016 2015 2014 2013 2012 2011 2010 2009\n",
      " 2008 2023 2022 1970 1992 1991 1990 1989 1988 1987 1986 1985 1984 1983\n",
      " 1982 1981 1980 1979 1978 1977 1976 1975 1974 1973 1972 1971 2024 1962\n",
      " 1961 1960 1959 1969 1968 1967 1966 1965 1964 1963 1958 1957 1956 1955\n",
      " 1954 1953 1952 1951 1950]\n",
      "OBS_VALUE >>> [ 1.14528675e+07  5.67492675e+06  5.42313408e+06 ... -4.71392800e+03\n",
      "  1.14448800e+03  4.86579000e+02]\n",
      "REF_YEAR_PRICE >>> [nan 2022.0 2015.0 2020.0 2018.0 2010.0 2021.0 1991.0 2017.0 2016.0 '2020'\n",
      " '2015' '2022' '2021' '2009-2010' '2018' '2017' '2011-2012' '2016'\n",
      " '2022-2023' '2010' '1991' '2005' '2009' 1995.0]\n",
      "OBS_STATUS >>> ['A' 'E' 'P' 'B' 'D']\n",
      "Observation status >>> ['Normal value' 'Estimated value' 'Provisional value' 'Time series break'\n",
      " 'Definition differs']\n",
      "UNIT_MULT >>> [6 0]\n",
      "Unit multiplier >>> ['Millions' 'Units']\n",
      "CURRENCY >>> ['XAF' 'GBP' '_Z' 'CHF' 'EUR' 'NOK' 'CRC' 'SEK' 'AUD' 'CLP' 'CZK' 'BRL'\n",
      " 'COP' 'ILS' 'NZD' 'CNY' 'MAD' 'USD' 'MXN' 'HKD' 'RON' 'KRW' 'JPY' 'DKK'\n",
      " 'HUF' 'CAD' 'XOF' 'IDR' 'GEL' 'SAR' 'PLN' 'BGN' 'RUB' 'TRY' 'ZAR' 'ISK'\n",
      " 'INR' 'RSD' 'MKD' 'SGD' 'CVE' 'ALL']\n",
      "Currency >>> ['CFA franc - BEAC' 'Pound sterling' 'Not applicable' 'Swiss franc' 'Euro'\n",
      " 'Norwegian krone' 'Costa Rican colon' 'Swedish krona' 'Australian dollar'\n",
      " 'Chilean peso' 'Czech koruna' 'Brazilian real' 'Colombian peso'\n",
      " 'New Israeli sheqel' 'New Zealand dollar' 'Chinese yuan renminbi'\n",
      " 'Moroccan dirham' 'US dollar' 'Mexican peso' 'Hong Kong dollar'\n",
      " 'Romanian leu' 'Won' 'Yen' 'Danish krone' 'Forint' 'Canadian dollar'\n",
      " 'CFA Franc - BCEAO' 'Rupiah' 'Lari' 'Saudi riyal' 'Zloty' 'Bulgarian lev'\n",
      " 'Russian ruble' 'Turkish lira' 'Rand' 'Iceland krona' 'Indian rupee'\n",
      " 'Serbian dinar' 'Denar' 'Singapore dollar' 'Cape Verde escudo' 'Lek']\n"
     ]
    }
   ],
   "source": [
    "for col in data.columns:\n",
    "    print(col, '>>>', data[col].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9dc10725",
   "metadata": {},
   "outputs": [],
   "source": [
    "def filter_dataframe(df, search_dic):\n",
    "    \"\"\"\n",
    "    根据字典筛选 DataFrame 的列。\n",
    "\n",
    "    :param df: 要筛选的 DataFrame\n",
    "    :param search_dic: 筛选条件字典，键为列名，值为筛选值\n",
    "    :return: 筛选后的 DataFrame\n",
    "    \"\"\"\n",
    "    conditions = pd.Series([True] * len(df))\n",
    "    for col, value in search_dic.items():\n",
    "        conditions &= df[col] == value\n",
    "    return df[conditions]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dbdf01d9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>REF_AREA</th>\n",
       "      <th>Reference area</th>\n",
       "      <th>TRANSACTION</th>\n",
       "      <th>Transaction</th>\n",
       "      <th>UNIT_MEASURE</th>\n",
       "      <th>Unit of measure</th>\n",
       "      <th>PRICE_BASE</th>\n",
       "      <th>Price base</th>\n",
       "      <th>TIME_PERIOD</th>\n",
       "      <th>OBS_VALUE</th>\n",
       "      <th>REF_YEAR_PRICE</th>\n",
       "      <th>OBS_STATUS</th>\n",
       "      <th>Observation status</th>\n",
       "      <th>UNIT_MULT</th>\n",
       "      <th>Unit multiplier</th>\n",
       "      <th>CURRENCY</th>\n",
       "      <th>Currency</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>149635</th>\n",
       "      <td>JPN</td>\n",
       "      <td>Japan</td>\n",
       "      <td>B1GQ</td>\n",
       "      <td>Gross domestic product</td>\n",
       "      <td>XDC</td>\n",
       "      <td>National currency</td>\n",
       "      <td>V</td>\n",
       "      <td>Current prices</td>\n",
       "      <td>2020</td>\n",
       "      <td>539646000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A</td>\n",
       "      <td>Normal value</td>\n",
       "      <td>6</td>\n",
       "      <td>Millions</td>\n",
       "      <td>JPY</td>\n",
       "      <td>Yen</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       REF_AREA Reference area TRANSACTION             Transaction  \\\n",
       "149635      JPN          Japan        B1GQ  Gross domestic product   \n",
       "\n",
       "       UNIT_MEASURE    Unit of measure PRICE_BASE      Price base  \\\n",
       "149635          XDC  National currency          V  Current prices   \n",
       "\n",
       "        TIME_PERIOD    OBS_VALUE REF_YEAR_PRICE OBS_STATUS Observation status  \\\n",
       "149635         2020  539646000.0            NaN          A       Normal value   \n",
       "\n",
       "        UNIT_MULT Unit multiplier CURRENCY Currency  \n",
       "149635          6        Millions      JPY      Yen  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "search_dic = {'Reference area': 'Japan', 'Transaction': 'Gross domestic product', 'TIME_PERIOD': 2020, 'Unit of measure': 'National currency', 'Price base':'Current prices'}\n",
    "japan_2020 = filter_dataframe(data, search_dic)\n",
    "japan_2020"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8126166d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "539646000.0"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "japan_2020['OBS_VALUE'].values[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6ef44650",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>REF_AREA</th>\n",
       "      <th>Reference area</th>\n",
       "      <th>TRANSACTION</th>\n",
       "      <th>Transaction</th>\n",
       "      <th>UNIT_MEASURE</th>\n",
       "      <th>Unit of measure</th>\n",
       "      <th>PRICE_BASE</th>\n",
       "      <th>Price base</th>\n",
       "      <th>TIME_PERIOD</th>\n",
       "      <th>OBS_VALUE</th>\n",
       "      <th>REF_YEAR_PRICE</th>\n",
       "      <th>OBS_STATUS</th>\n",
       "      <th>Observation status</th>\n",
       "      <th>UNIT_MULT</th>\n",
       "      <th>Unit multiplier</th>\n",
       "      <th>CURRENCY</th>\n",
       "      <th>Currency</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>168556</th>\n",
       "      <td>JPN</td>\n",
       "      <td>Japan</td>\n",
       "      <td>B6N</td>\n",
       "      <td>Disposable income, net</td>\n",
       "      <td>XDC</td>\n",
       "      <td>National currency</td>\n",
       "      <td>V</td>\n",
       "      <td>Current prices</td>\n",
       "      <td>2020</td>\n",
       "      <td>421131100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A</td>\n",
       "      <td>Normal value</td>\n",
       "      <td>6</td>\n",
       "      <td>Millions</td>\n",
       "      <td>JPY</td>\n",
       "      <td>Yen</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       REF_AREA Reference area TRANSACTION             Transaction  \\\n",
       "168556      JPN          Japan         B6N  Disposable income, net   \n",
       "\n",
       "       UNIT_MEASURE    Unit of measure PRICE_BASE      Price base  \\\n",
       "168556          XDC  National currency          V  Current prices   \n",
       "\n",
       "        TIME_PERIOD    OBS_VALUE REF_YEAR_PRICE OBS_STATUS Observation status  \\\n",
       "168556         2020  421131100.0            NaN          A       Normal value   \n",
       "\n",
       "        UNIT_MULT Unit multiplier CURRENCY Currency  \n",
       "168556          6        Millions      JPY      Yen  "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp_v = filter_dataframe(data, {'Reference area': 'Japan', 'Transaction': 'Disposable income, net', 'TIME_PERIOD': 2020, 'Unit of measure': 'National currency', 'Price base':'Current prices'})\n",
    "temp_v"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0c9d143f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "421131100.0"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp_v['OBS_VALUE'].values[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "90367d26",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.7803839924691371"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp_v['OBS_VALUE'].values[0] / japan_2020['OBS_VALUE'].values[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5c758da5",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_ratio_in_a_country(country_str):\n",
    "    dpi_ = filter_dataframe(data, {'Reference area': country_str, 'Transaction': 'Disposable income, net', 'TIME_PERIOD': 2020, 'Unit of measure': 'National currency', 'Price base':'Current prices'})\n",
    "    gdp_ = filter_dataframe(data, {'Reference area': country_str, 'Transaction': 'Gross domestic product', 'TIME_PERIOD': 2020, 'Unit of measure': 'National currency', 'Price base':'Current prices'})\n",
    "    if len(dpi_) != 1 or len(gdp_) != 1:\n",
    "        return None\n",
    "    # print(dpi_['OBS_VALUE'])\n",
    "    # print(gdp_['OBS_VALUE'])\n",
    "    return dpi_['OBS_VALUE'].values[0] / gdp_['OBS_VALUE'].values[0]\n",
    "\n",
    "def get_ratio_in_a_country_many_years(country_str):\n",
    "    dpi_ = filter_dataframe(data, {'Reference area': country_str, 'Transaction': 'Disposable income, net', 'Unit of measure': 'National currency', 'Price base':'Current prices'})\n",
    "    gdp_ = filter_dataframe(data, {'Reference area': country_str, 'Transaction': 'Gross domestic product', 'Unit of measure': 'National currency', 'Price base':'Current prices'})\n",
    "\n",
    "    return dpi_, gdp_\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6ae2635f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Cameroon None \n",
      "\n",
      "United Kingdom 0.8028168478421059 \n",
      "\n",
      "Ireland 0.4415672277822175 \n",
      "\n",
      "Switzerland 0.6908782138411282 \n",
      "\n",
      "Italy 0.8031509835349078 \n",
      "\n",
      "Russia None \n",
      "\n",
      "Canada 0.812528737547438 \n",
      "\n",
      "Latvia 0.8268936783516753 \n",
      "\n",
      "France 0.8098667880902197 \n",
      "\n",
      "Austria 0.7913398238684006 \n",
      "\n",
      "Georgia 0.9429702073196043 \n",
      "\n",
      "Norway 0.8193238626925605 \n",
      "\n",
      "Portugal 0.8185181112695072 \n",
      "\n",
      "Lithuania 0.852006243677584 \n",
      "\n",
      "Hungary 0.7992665142674221 \n",
      "\n",
      "Costa Rica 0.8962017296310718 \n",
      "\n",
      "United States 0.8277703514148684 \n",
      "\n",
      "Sweden 0.8450578751788994 \n",
      "\n",
      "Euro area (20 countries) 0.7971246705974198 \n",
      "\n",
      "Australia 0.8128493227130599 \n",
      "\n",
      "Belgium 0.7954529037032596 \n",
      "\n",
      "Romania None \n",
      "\n",
      "Slovak Republic 0.7847291932027181 \n",
      "\n",
      "Chile 0.9359625189472932 \n",
      "\n",
      "Slovenia 0.8009332480563911 \n",
      "\n",
      "Spain 0.8231442401528851 \n",
      "\n",
      "Senegal None \n",
      "\n",
      "Greece 0.8330749707872336 \n",
      "\n",
      "Finland 0.8049765850067897 \n",
      "\n",
      "China (People’s Republic of) None \n",
      "\n",
      "European Union (27 countries from 01/02/2020) 0.8066520352290244 \n",
      "\n",
      "Colombia None \n",
      "\n",
      "Croatia 0.8662381233372819 \n",
      "\n",
      "South Africa 0.840911003575446 \n",
      "\n",
      "Germany 0.8166224105843543 \n",
      "\n",
      "Mexico 0.795276171630415 \n",
      "\n",
      "Netherlands 0.7849590244750834 \n",
      "\n",
      "Israel 0.8596227932257884 \n",
      "\n",
      "Czechia 0.7153269605399019 \n",
      "\n",
      "Brazil 0.980569141834975 \n",
      "\n",
      "Estonia 0.8186207119346144 \n",
      "\n",
      "Japan 0.7803839924691371 \n",
      "\n",
      "Former Federal Republic of Germany None \n",
      "\n",
      "Türkiye 0.8159081941651574 \n",
      "\n",
      "Poland 0.8505228089613269 \n",
      "\n",
      "Denmark 0.8417475002063104 \n",
      "\n",
      "Luxembourg 0.576863290264288 \n",
      "\n",
      "New Zealand 0.8358191268536056 \n",
      "\n",
      "European Union (15 countries) None \n",
      "\n",
      "Morocco None \n",
      "\n",
      "Hong Kong (China) None \n",
      "\n",
      "Korea 0.8010421835866651 \n",
      "\n",
      "Saudi Arabia 0.8585601504633845 \n",
      "\n",
      "Singapore None \n",
      "\n",
      "Iceland None \n",
      "\n",
      "Malta None \n",
      "\n",
      "Indonesia None \n",
      "\n",
      "OECD Europe None \n",
      "\n",
      "OECD (26 countries) None \n",
      "\n",
      "Bulgaria 0.839541211924216 \n",
      "\n",
      "OECD None \n",
      "\n",
      "Cabo Verde None \n",
      "\n",
      "India None \n",
      "\n",
      "Serbia None \n",
      "\n",
      "North Macedonia None \n",
      "\n",
      "Cyprus None \n",
      "\n",
      "Albania None \n",
      "\n"
     ]
    }
   ],
   "source": [
    "for cty in ['Cameroon' ,'United Kingdom' ,'Ireland', 'Switzerland' ,'Italy' ,'Russia',\n",
    "            'Canada', 'Latvia', 'France', 'Austria', 'Georgia', 'Norway', 'Portugal',\n",
    "            'Lithuania', 'Hungary', 'Costa Rica', 'United States', 'Sweden',\n",
    "            'Euro area (20 countries)', 'Australia', 'Belgium', 'Romania',\n",
    "            'Slovak Republic', 'Chile', 'Slovenia', 'Spain', 'Senegal', 'Greece', 'Finland',\n",
    "            'China (People’s Republic of)',\n",
    "            'European Union (27 countries from 01/02/2020)', 'Colombia', 'Croatia',\n",
    "            'South Africa', 'Germany', 'Mexico', 'Netherlands', 'Israel', 'Czechia',\n",
    "            'Brazil', 'Estonia', 'Japan','Former Federal Republic of Germany', 'Türkiye',\n",
    "            'Poland', 'Denmark', 'Luxembourg', 'New Zealand',\n",
    "            'European Union (15 countries)', 'Morocco', 'Hong Kong (China)', 'Korea',\n",
    "            'Saudi Arabia', 'Singapore', 'Iceland', 'Malta', 'Indonesia', 'OECD Europe',\n",
    "            'OECD (26 countries)', 'Bulgaria', 'OECD', 'Cabo Verde', 'India', 'Serbia',\n",
    "            'North Macedonia', 'Cyprus', 'Albania']:\n",
    "   v = get_ratio_in_a_country(cty)\n",
    "   print(cty, v, '\\n')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "247357a5",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a6915649",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "122904    2817037.0\n",
      "Name: OBS_VALUE, dtype: float64\n",
      "211078    3449620.0\n",
      "Name: OBS_VALUE, dtype: float64\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0.8166224105843543"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_ratio_in_a_country('Germany')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c47eed55",
   "metadata": {},
   "outputs": [],
   "source": [
    "dpi_,gdp_ = get_ratio_in_a_country_many_years('United States')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "92efcfef",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TIME_PERIOD</th>\n",
       "      <th>OBS_VALUE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>267998</th>\n",
       "      <td>2023</td>\n",
       "      <td>22769799.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>267997</th>\n",
       "      <td>2022</td>\n",
       "      <td>21707104.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>267996</th>\n",
       "      <td>2021</td>\n",
       "      <td>19788860.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>267995</th>\n",
       "      <td>2020</td>\n",
       "      <td>17676295.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>267994</th>\n",
       "      <td>2019</td>\n",
       "      <td>18135457.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>267993</th>\n",
       "      <td>2018</td>\n",
       "      <td>17432391.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>267992</th>\n",
       "      <td>2017</td>\n",
       "      <td>16560580.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>267991</th>\n",
       "      <td>2016</td>\n",
       "      <td>15845813.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>267990</th>\n",
       "      <td>2015</td>\n",
       "      <td>15566298.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>267989</th>\n",
       "      <td>2014</td>\n",
       "      <td>15063195.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>267988</th>\n",
       "      <td>2013</td>\n",
       "      <td>14356134.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>267987</th>\n",
       "      <td>2012</td>\n",
       "      <td>13959677.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>267986</th>\n",
       "      <td>2011</td>\n",
       "      <td>13268939.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>267985</th>\n",
       "      <td>2010</td>\n",
       "      <td>12681658.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>267984</th>\n",
       "      <td>2009</td>\n",
       "      <td>11951116.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>267983</th>\n",
       "      <td>2008</td>\n",
       "      <td>12256336.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>267982</th>\n",
       "      <td>2007</td>\n",
       "      <td>12204255.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>267981</th>\n",
       "      <td>2006</td>\n",
       "      <td>11868356.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>267980</th>\n",
       "      <td>2005</td>\n",
       "      <td>11113869.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268045</th>\n",
       "      <td>2004</td>\n",
       "      <td>10419830.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268044</th>\n",
       "      <td>2003</td>\n",
       "      <td>9755867.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268043</th>\n",
       "      <td>2002</td>\n",
       "      <td>9337100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268042</th>\n",
       "      <td>2001</td>\n",
       "      <td>9084482.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268041</th>\n",
       "      <td>2000</td>\n",
       "      <td>8819694.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268040</th>\n",
       "      <td>1999</td>\n",
       "      <td>8244477.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268039</th>\n",
       "      <td>1998</td>\n",
       "      <td>7766853.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268038</th>\n",
       "      <td>1997</td>\n",
       "      <td>7299848.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268037</th>\n",
       "      <td>1996</td>\n",
       "      <td>6822140.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268036</th>\n",
       "      <td>1995</td>\n",
       "      <td>6409111.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268035</th>\n",
       "      <td>1994</td>\n",
       "      <td>6069271.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268034</th>\n",
       "      <td>1993</td>\n",
       "      <td>5685435.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268033</th>\n",
       "      <td>1992</td>\n",
       "      <td>5433736.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268032</th>\n",
       "      <td>1991</td>\n",
       "      <td>5169525.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268031</th>\n",
       "      <td>1990</td>\n",
       "      <td>4982217.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268030</th>\n",
       "      <td>1989</td>\n",
       "      <td>4729885.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268029</th>\n",
       "      <td>1988</td>\n",
       "      <td>4442788.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268028</th>\n",
       "      <td>1987</td>\n",
       "      <td>4071902.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268027</th>\n",
       "      <td>1986</td>\n",
       "      <td>3803682.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268026</th>\n",
       "      <td>1985</td>\n",
       "      <td>3644482.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268025</th>\n",
       "      <td>1984</td>\n",
       "      <td>3410443.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268055</th>\n",
       "      <td>1983</td>\n",
       "      <td>3031153.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268054</th>\n",
       "      <td>1982</td>\n",
       "      <td>2814851.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268053</th>\n",
       "      <td>1981</td>\n",
       "      <td>2698006.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268052</th>\n",
       "      <td>1980</td>\n",
       "      <td>2406172.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268051</th>\n",
       "      <td>1979</td>\n",
       "      <td>2229827.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268050</th>\n",
       "      <td>1978</td>\n",
       "      <td>2013819.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268049</th>\n",
       "      <td>1977</td>\n",
       "      <td>1784728.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268048</th>\n",
       "      <td>1976</td>\n",
       "      <td>1601252.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268047</th>\n",
       "      <td>1975</td>\n",
       "      <td>1436900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268046</th>\n",
       "      <td>1974</td>\n",
       "      <td>1337671.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268059</th>\n",
       "      <td>1973</td>\n",
       "      <td>1245298.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268058</th>\n",
       "      <td>1972</td>\n",
       "      <td>1110271.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268057</th>\n",
       "      <td>1971</td>\n",
       "      <td>1006098.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268056</th>\n",
       "      <td>1970</td>\n",
       "      <td>930903.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        TIME_PERIOD   OBS_VALUE\n",
       "267998         2023  22769799.0\n",
       "267997         2022  21707104.0\n",
       "267996         2021  19788860.0\n",
       "267995         2020  17676295.0\n",
       "267994         2019  18135457.0\n",
       "267993         2018  17432391.0\n",
       "267992         2017  16560580.0\n",
       "267991         2016  15845813.0\n",
       "267990         2015  15566298.0\n",
       "267989         2014  15063195.0\n",
       "267988         2013  14356134.0\n",
       "267987         2012  13959677.0\n",
       "267986         2011  13268939.0\n",
       "267985         2010  12681658.0\n",
       "267984         2009  11951116.0\n",
       "267983         2008  12256336.0\n",
       "267982         2007  12204255.0\n",
       "267981         2006  11868356.0\n",
       "267980         2005  11113869.0\n",
       "268045         2004  10419830.0\n",
       "268044         2003   9755867.0\n",
       "268043         2002   9337100.0\n",
       "268042         2001   9084482.0\n",
       "268041         2000   8819694.0\n",
       "268040         1999   8244477.0\n",
       "268039         1998   7766853.0\n",
       "268038         1997   7299848.0\n",
       "268037         1996   6822140.0\n",
       "268036         1995   6409111.0\n",
       "268035         1994   6069271.0\n",
       "268034         1993   5685435.0\n",
       "268033         1992   5433736.0\n",
       "268032         1991   5169525.0\n",
       "268031         1990   4982217.0\n",
       "268030         1989   4729885.0\n",
       "268029         1988   4442788.0\n",
       "268028         1987   4071902.0\n",
       "268027         1986   3803682.0\n",
       "268026         1985   3644482.0\n",
       "268025         1984   3410443.0\n",
       "268055         1983   3031153.0\n",
       "268054         1982   2814851.0\n",
       "268053         1981   2698006.0\n",
       "268052         1980   2406172.0\n",
       "268051         1979   2229827.0\n",
       "268050         1978   2013819.0\n",
       "268049         1977   1784728.0\n",
       "268048         1976   1601252.0\n",
       "268047         1975   1436900.0\n",
       "268046         1974   1337671.0\n",
       "268059         1973   1245298.0\n",
       "268058         1972   1110271.0\n",
       "268057         1971   1006098.0\n",
       "268056         1970    930903.0"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dpi_.loc[:,['TIME_PERIOD', 'OBS_VALUE']].sort_values(by='TIME_PERIOD', ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0d5a1de3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TIME_PERIOD</th>\n",
       "      <th>OBS_VALUE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>104177</th>\n",
       "      <td>2020</td>\n",
       "      <td>21354105.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        TIME_PERIOD   OBS_VALUE\n",
       "104177         2020  21354105.0"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdp_.loc[:,['TIME_PERIOD', 'OBS_VALUE']][gdp_.TIME_PERIOD == 2020]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "257eb739",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TIME_PERIOD</th>\n",
       "      <th>OBS_VALUE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>267992</th>\n",
       "      <td>2017</td>\n",
       "      <td>16560580.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        TIME_PERIOD   OBS_VALUE\n",
       "267992         2017  16560580.0"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dpi_.loc[:,['TIME_PERIOD', 'OBS_VALUE']][dpi_.TIME_PERIOD == 2017]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4ef6aaa7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.8389164356054106"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dpi_.loc[:,['TIME_PERIOD', 'OBS_VALUE']][dpi_.TIME_PERIOD == 1995]['OBS_VALUE'].values[0] /gdp_.loc[:,['TIME_PERIOD', 'OBS_VALUE']][gdp_.TIME_PERIOD == 1995]['OBS_VALUE'].values[0]"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
